Set Auto Increment Starting Value {postgres}
들어가기 전에,
sequence 이름찾는 방법부터 알아보자.
SELECT pg_get_serial_sequence('image', 'imgId');
또는 모든 sequence를 찾고싶다면..
SELECT * FROM pg_sequences;
sequence 이름 찾는 방법 (GPT 응답)
Since your sequence is named public."image_imgId_seq"
, you can reconfigure the increment value using the following steps:
1. Update the Increment Value
To change the increment value (how much the sequence increases per new entry):
ALTER SEQUENCE public."image_imgId_seq" INCREMENT BY 1;
(Change 1
to any desired increment.)
2. Restart the Sequence with a Specific Value
If you want the next generated ID to start at 1000, run:
ALTER SEQUENCE public."image_imgId_seq" RESTART WITH 1000;
Alternatively, use:
SELECT setval('public."image_imgId_seq"', 1000, false);
- First argument (
1000
) → Sets the current value. - Second argument (
false
) → Ensures the nextnextval()
call returns1000
, not1001
.
3. Verify the Change
Run:
SELECT nextval('public."image_imgId_seq"');
This should return 1000 (or your desired starting value).
4. Ensure the Sequence is Linked to Your Column
Make sure the image_imgId
column uses the sequence:
SELECT column_default
FROM information_schema.columns
WHERE table_name = 'image' AND column_name = 'imgId';
If the output does not contain nextvalregclass
, manually link the sequence:
ALTER TABLE image ALTER COLUMN imgId SET DEFAULT nextval('public."image_imgId_seq"');
5. Test with an Insert
INSERT INTO image DEFAULT VALUES RETURNING imgId;
This should return 1000 if everything is configured correctly.
Summary
✅ Set increment value: ALTER SEQUENCE public."image_imgId_seq" INCREMENT BY 1;
✅ Restart at 1000: ALTER SEQUENCE public."image_imgId_seq" RESTART WITH 1000;
✅ Manually set value: SELECT setval('public."image_imgId_seq"', 1000, false);
✅ Ensure the column uses the sequence: ALTER TABLE image ALTER COLUMN imgId SET DEFAULT nextval('public."image_imgId_seq"');
✅ Test with INSERT
Let me know if you need further help! 🚀